package com.sec.etech.common.util;

import cn.hutool.core.util.StrUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;

public class ExcelUtil {

    /**
     * 复制指定excel的行到指定行
     *
     * @param exTemplateFilePath 原始文件
     * @param targetFilePath     目标文件
     * @param startRow           开始行
     * @param endRow             结束行
     * @param targetPosition     目标行
     * @throws Exception
     */
    public static void copyExcelRows(String exTemplateFilePath, String targetFilePath, int startRow, int endRow, int targetPosition) throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(exTemplateFilePath));
        XSSFSheet sheet = wb.getSheetAt(0);
        copyRows(sheet, wb.getSheetAt(0), startRow, endRow, targetPosition);
        FileOutputStream fileOut = new FileOutputStream(targetFilePath);
        wb.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }

    /**
     * 复制excel行
     *
     * @param sourceSheet    原始sheet
     * @param targetSheet    目标sheet
     * @param startRow       开始行
     * @param endRow         结束行
     * @param targetPosition 目标行
     */
    @SuppressWarnings("all")
    public static void copyRows(XSSFSheet sourceSheet, XSSFSheet targetSheet, int startRow, int endRow, int targetPosition) {
        int pStartRow = startRow - 1;
        int pEndRow = endRow - 1;
        int pPosition = targetPosition - 1;
        XSSFRow sourceRow = null, targetRow = null;
        XSSFCell sourceCell = null, targetCell = null;
        CellRangeAddress region = null;
        CellType cType;
//        int cType;
        int i, j, targetRowFrom, targetRowTo;
        if (pStartRow < 0 || pEndRow < 0 || pStartRow > pEndRow) {
            return;
        }
        // 获取合并行单元格的数目,并设定目标单元格合并属性
        int mergedc = sourceSheet.getNumMergedRegions();
        for (i = 0; i < mergedc; i++) {
            region = sourceSheet.getMergedRegion(i);
            int rf = region.getFirstRow();
            int rt = region.getLastRow();
            if ((rf >= pStartRow) && (rt <= pEndRow)) {
                targetRowFrom = rf - pStartRow + pPosition;
                targetRowTo = rt - pStartRow + pPosition;
                region.setFirstRow(targetRowFrom);
                region.setLastRow(targetRowTo);
                targetSheet.addMergedRegion(region);
            }
        }
        //设定个单元格的列宽
        for (i = pStartRow; i <= pEndRow; i++) {
            sourceRow = sourceSheet.getRow(i);
            if (sourceRow != null) {
                int firstC = sourceRow.getFirstCellNum();
                for (j = sourceRow.getLastCellNum(); j > firstC; j--) {
                    targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j));
                    targetSheet.setColumnHidden(j, false);
                }
                break;
            }
        }
        //填充数据
        for (; i <= pEndRow; i++) {
            sourceRow = sourceSheet.getRow(i);
            if (sourceRow == null) {
                continue;
            }
            targetRow = targetSheet.createRow(i - pStartRow + pPosition);
            targetRow.setHeight(sourceRow.getHeight());
            int psy = sourceRow.getPhysicalNumberOfCells();
            for (j = sourceRow.getFirstCellNum(); j < psy; j++) {
                sourceCell = sourceRow.getCell(j);
                if (sourceCell == null) {
                    continue;
                }
                targetCell = targetRow.createCell(j);
                targetCell.setCellStyle(sourceCell.getCellStyle());
                cType = sourceCell.getCellType();
                targetCell.setCellType(cType);
                if(cType.equals(CellType.BOOLEAN)){
                    targetCell.setCellValue(sourceCell.getBooleanCellValue());
                }else if(cType.equals(CellType.ERROR)){
                    targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
                }else if(cType.equals(CellType.FORMULA)){
                    targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula()));
                }else if(cType.equals(CellType.NUMERIC)){
                    targetCell.setCellValue(sourceCell.getNumericCellValue());
                }else if(cType.equals(CellType.STRING)){
                    targetCell.setCellValue(sourceCell.getRichStringCellValue());
                }
               /* switch (cType) {
                    case HSSFCell.CELL_TYPE_BOOLEAN:
                        targetCell.setCellValue(sourceCell.getBooleanCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_ERROR:
                        targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_FORMULA:
                        targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula()));
                        break;
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        targetCell.setCellValue(sourceCell.getNumericCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        targetCell.setCellValue(sourceCell.getRichStringCellValue());
                        break;
                }*/
            }
        }
    }

    /**
     * 解析公式
     *
     * @param pPOIFormula
     * @return
     */
    private static String parseFormula(String pPOIFormula) {
        final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
        StringBuffer result = null;
        int index;
        result = new StringBuffer();
        index = pPOIFormula.indexOf(cstReplaceString);
        if (index >= 0) {
            result.append(pPOIFormula.substring(0, index));
            result.append(pPOIFormula.substring(index + cstReplaceString.length()));
        } else {
            result.append(pPOIFormula);
        }
        return result.toString();
    }
}
